﻿/*----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------
  -- MEMBERSHIP --
  ----------------------------------------------------------------------------------------------------------------  
  ---------------------------------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[aspnet_Membership_GetPasswordWithFormat]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
GO

--EXEC [dbo].[aspnet_Membership_GetPasswordWithFormat]
--	@ApplicationName=N'/',
--	@UserName=N'manhnv83',
--	@UpdateLastLoginActivityDate=1,
--	@CurrentTimeUtc='2014-09-24 05:58:02.410'

CREATE PROCEDURE [dbo].[aspnet_Membership_GetPasswordWithFormat]
	@ApplicationName [nvarchar](256),
	@UserName [nvarchar](256),
	@UpdateLastLoginActivityDate [bit],
	@CurrentTimeUtc [datetime]
WITH EXECUTE AS CALLER
AS
BEGIN
    DECLARE @IsLockedOut                        bit
    DECLARE @UserId                             uniqueidentifier
    DECLARE @Password                           nvarchar(128)
    DECLARE @PasswordSalt                       nvarchar(128)
    DECLARE @PasswordFormat                     int
    DECLARE @FailedPasswordAttemptCount         int
    DECLARE @FailedPasswordAnswerAttemptCount   int
    DECLARE @IsApproved                         bit
    DECLARE @LastActivityDate                   datetime
    DECLARE @LastLoginDate                      datetime

    SELECT  @UserId          = NULL

    SELECT  @UserId = u.UserId, @IsLockedOut = m.IsLockedOut, @Password=Password, @PasswordFormat=PasswordFormat,
            @PasswordSalt=PasswordSalt, @FailedPasswordAttemptCount=FailedPasswordAttemptCount,
		    @FailedPasswordAnswerAttemptCount=FailedPasswordAnswerAttemptCount, @IsApproved=IsApproved,
            @LastActivityDate = LastActivityDate, @LastLoginDate = LastLoginDate
    FROM    dbo.aspnet_Applications a, dbo.aspnet_Users u, dbo.aspnet_Membership m
    WHERE   LOWER(@ApplicationName) = a.LoweredApplicationName AND
            u.ApplicationId = a.ApplicationId    AND
            u.UserId = m.UserId AND
            LOWER(@UserName) = u.LoweredUserName

    IF (@UserId IS NULL)
        RETURN 1

    IF (@IsLockedOut = 1)
        RETURN 99

    SELECT   @Password, @PasswordFormat, @PasswordSalt, @FailedPasswordAttemptCount,
             @FailedPasswordAnswerAttemptCount, @IsApproved, @LastLoginDate, @LastActivityDate

    IF (@UpdateLastLoginActivityDate = 1 AND @IsApproved = 1)
    BEGIN
        UPDATE  dbo.aspnet_Membership
        SET     LastLoginDate = @CurrentTimeUtc
        WHERE   UserId = @UserId

        UPDATE  dbo.aspnet_Users
        SET     LastActivityDate = @CurrentTimeUtc
        WHERE   @UserId = UserId
    END

    RETURN 0
END
GO

----------------------------------------------------------------------------------------------------------------  

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetAuthorize]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetAuthorize]
GO

--EXEC [dbo].[uspGetAuthorize] @OrgId='A001', @UserId='A89B1ACD-9501-6AE6-B9C8-AABB07DA2010'

CREATE PROCEDURE [dbo].[uspGetAuthorize]	
	@OrgId [varchar](10),
	@UserId [varchar](40)
AS
BEGIN
	--DECLARE @uid as uniqueidentifier
	--SELECT @uid = dbo.ufnGetUniqueIdentifier(@UserId)
	SELECT OrgId,UserId,RoleId,CtrIds FROM [dbo].[UsersInRoles]
	WHERE OrgId = @OrgId AND @UserId = @UserId
END
GO

/*----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------
  -- FUNCTION --
  ----------------------------------------------------------------------------------------------------------------  
  ---------------------------------------------------------------------------------------------------------------*/

IF OBJECT_ID (N'dbo.ufnGetUniqueIdentifier') IS NOT NULL
    DROP FUNCTION dbo.ufnGetUniqueIdentifier;
GO

CREATE FUNCTION dbo.ufnGetUniqueIdentifier(@Id VARCHAR(50))
RETURNS UNIQUEIDENTIFIER
AS 
-- SELECT dbo.ufnGetUniqueIdentifier('a89b1acd95016ae6b9c8aabb07da2010') --A89B1ACD-9501-6AE6-B9C8-AABB07DA2010
BEGIN
    DECLARE @RESULT_UNIQUE UNIQUEIDENTIFIER

	SELECT @RESULT_UNIQUE = CAST(
        SUBSTRING(@Id, 1, 8) + '-' + SUBSTRING(@Id, 9, 4) + '-' + SUBSTRING(@Id, 13, 4) + '-' +
        SUBSTRING(@Id, 17, 4) + '-' + SUBSTRING(@Id, 21, 12)
        AS UNIQUEIDENTIFIER)

    RETURN @RESULT_UNIQUE;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetTableFromIntList') IS NOT NULL
    DROP FUNCTION dbo.ufnGetTableFromIntList;
GO

CREATE FUNCTION [dbo].[ufnGetTableFromIntList]
(
	@strIntList VARCHAR(MAX),
	@strDelimiter VARCHAR(10)
)
 
RETURNS @tblList TABLE (IntValue INT NOT NULL)
 
AS
 
BEGIN
 
DECLARE    @iStartPos INT,@iEndPos INT,@strValue VARCHAR(15)
SET @iStartPos = 1
SET @strIntList = @strIntList + @strDelimiter
SET @iEndPos = CHARINDEX(@strDelimiter, @strIntList)
 
WHILE @iEndPos > 0
 
BEGIN
 
    SET @strValue = SUBSTRING(@strIntList, @iStartPos, @iEndPos - @iStartPos)
    INSERT @tblList (IntValue) VALUES(CONVERT(INT, @strValue))
    SET @iStartPos = @iEndPos + 1
    SET @iEndPos = CHARINDEX(@strDelimiter, @strIntList, @iStartPos)
END
RETURN
END
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnSplit') IS NOT NULL
DROP FUNCTION [dbo].[ufnSplit];
GO

CREATE FUNCTION [dbo].[ufnSplit]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE 
AS
--DECLARE @DelimitedString NVARCHAR(128)
--SET @DelimitedString = 'Duckman,Cornfed,Ajax,Charles,Mambo'
--SELECT * FROM [dbo].[ufnSplit](@DelimitedString, ',')
RETURN 
(
    WITH Split(stpos,endpos) 
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnSplitString') IS NOT NULL
    DROP FUNCTION dbo.ufnSplitString;
GO

CREATE FUNCTION dbo.ufnSplitString(@stringToSplit VARCHAR(MAX) )
RETURNS
 @returnList TABLE ([Name] [nvarchar] (500))
AS
-- SELECT * FROM dbo.ufnSplitString('91,12,65,78,56,789')
BEGIN

 DECLARE @name NVARCHAR(255)
 DECLARE @pos INT

 WHILE CHARINDEX(',', @stringToSplit) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @stringToSplit)  
  SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @name

  SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
 END

 INSERT INTO @returnList
 SELECT @stringToSplit

 RETURN
END
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetAppId') IS NOT NULL
    DROP FUNCTION dbo.ufnGetAppId;
GO

CREATE FUNCTION dbo.ufnGetAppId(@AppName NVARCHAR(256))
RETURNS UNIQUEIDENTIFIER
AS 
-- SELECT dbo.ufnGetAppId(N'/') --C238AC15-580F-4286-8F64-00FF28665D68
BEGIN
    DECLARE @RESULT_UNIQUE UNIQUEIDENTIFIER
    SELECT @RESULT_UNIQUE = [ApplicationId] FROM [dbo].[aspnet_Applications] WHERE [ApplicationName] = @AppName
    RETURN @RESULT_UNIQUE;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetAppName') IS NOT NULL
DROP FUNCTION dbo.ufnGetAppName;
GO

CREATE FUNCTION dbo.ufnGetAppName(@AppId uniqueidentifier)
RETURNS NVARCHAR(256)
AS 
-- SELECT dbo.ufnGetAppName('C238AC15-580F-4286-8F64-00FF28665D68')
BEGIN
    DECLARE @RESULT_STRING NVARCHAR(256);
    SELECT @RESULT_STRING = [ApplicationName] FROM [dbo].[aspnet_Applications] WHERE [ApplicationId] = @AppId 
    RETURN @RESULT_STRING;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetName') IS NOT NULL
    DROP FUNCTION dbo.ufnGetName;
GO

CREATE FUNCTION dbo.ufnGetName(@Id uniqueidentifier)
RETURNS NVARCHAR(MAX)
AS 
-- SELECT dbo.ufnGetName(1)
BEGIN
    DECLARE @RESULT_STRING NVARCHAR(256);
    SELECT @RESULT_STRING = (U.LastName + ', ' + U.FirstName) FROM Users U WHERE U.UserId = @Id 
    RETURN @RESULT_STRING;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetOrgId') IS NOT NULL
    DROP FUNCTION dbo.ufnGetOrgId;
GO

CREATE FUNCTION dbo.ufnGetOrgId(@Name nvarchar(255))
RETURNS INTEGER
AS 
-- SELECT dbo.ufnGetOrgId(N'Phần mềm Kế toán')
BEGIN
    DECLARE @RESULT_INTEGER VARCHAR(10);
    SELECT @RESULT_INTEGER = Id FROM Organizations O WHERE O.Name = @Name
    RETURN @RESULT_INTEGER;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetOrgName') IS NOT NULL
    DROP FUNCTION dbo.ufnGetOrgName;
GO

CREATE FUNCTION dbo.ufnGetOrgName(@Id VARCHAR(10))
RETURNS NVARCHAR(256)
AS 
-- SELECT dbo.ufnGetOrgName(1)
BEGIN
    DECLARE @RESULT_STRING NVARCHAR(256);
    SELECT @RESULT_STRING = Name FROM Organizations O WHERE O.Id = @Id
    RETURN @RESULT_STRING;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetNameByUid') IS NOT NULL
DROP FUNCTION dbo.ufnGetNameByUid;
GO

CREATE FUNCTION dbo.ufnGetNameByUid(@UserId uniqueidentifier)
RETURNS NVARCHAR(256)
AS 
-- SELECT dbo.ufnGetNameByUid('02079A00-4D44-4D35-BAB4-C3C4BD0BF02F')
BEGIN
    DECLARE @RESULT_STRING NVARCHAR(256);
    SELECT @RESULT_STRING = (U.LastName + ', ' + U.FirstName) FROM Users U WHERE U.UserId = @UserId 
    RETURN @RESULT_STRING;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetFullNameById') IS NOT NULL
DROP FUNCTION dbo.ufnGetFullNameById;
GO

CREATE FUNCTION dbo.ufnGetFullNameById(@Id uniqueidentifier)
RETURNS NVARCHAR(256)
AS 
-- SELECT dbo.ufnGetFullNameById('')
BEGIN
    DECLARE @RESULT_STRING NVARCHAR(256);
    SELECT @RESULT_STRING = (U.LastName + ', ' + U.FirstName) FROM Users U WHERE U.UserId = @Id 
    RETURN @RESULT_STRING;
END;
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGetUserNameById') IS NOT NULL
DROP FUNCTION dbo.ufnGetUserNameById;
GO

CREATE FUNCTION dbo.ufnGetUserNameById(@Id uniqueidentifier)
RETURNS NVARCHAR(256)
AS 
-- SELECT dbo.ufnGetUserNameById(1)
BEGIN
    DECLARE @RESULT_STRING NVARCHAR(256);
    SELECT @RESULT_STRING = U.UserName FROM Users U WHERE U.UserId = @Id 
    RETURN @RESULT_STRING;
END;
GO

/*----------------------------------------------------------------------------------------------------------------
  -- OTHER FUNCTION --
  ----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------------------------------*/
IF OBJECT_ID (N'dbo.ufnGenerateIntegers') IS NOT NULL
DROP FUNCTION [dbo].[ufnGenerateIntegers];
GO

CREATE FUNCTION [dbo].[ufnGenerateIntegers] ( @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
-- SELECT * FROM [dbo].[ufnGenerateIntegers] (1000)
BEGIN
    DECLARE @Index    INT
    SET @Index = 1
    WHILE @Index <= @MaxValue
    BEGIN
        INSERT INTO @Integers ( [IntValue] ) VALUES ( @Index )
        SET @Index = @Index + 1
    END
    RETURN
END
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGenerateRangeIntegers') IS NOT NULL
DROP FUNCTION [dbo].[ufnGenerateRangeIntegers];
GO

CREATE FUNCTION [dbo].[ufnGenerateRangeIntegers] ( @MinValue INT, @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
-- SELECT * FROM [dbo].[ufnGenerateRangeIntegers] (100, 500)
BEGIN
    WHILE @MinValue <= @MaxValue
    BEGIN
        INSERT INTO @Integers ( [IntValue] ) VALUES ( @MinValue )
        SET @MinValue = @MinValue + 1
    END

    RETURN
END
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID (N'dbo.ufnGenerateListIntegers') IS NOT NULL
DROP FUNCTION [dbo].[ufnGenerateListIntegers];
GO

CREATE FUNCTION [dbo].[ufnGenerateListIntegers] ( @MaxValue INT )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
-- SELECT * FROM [dbo].[ufnGenerateListIntegers] (100)
BEGIN
    DECLARE @Digits TABLE ( [Digit] INT )
    DECLARE @Counter INT

    SET @Counter = 0
    WHILE @Counter < 10
    BEGIN
        INSERT INTO @Digits ( [Digit] ) VALUES ( @Counter )
        SET @Counter = @Counter + 1
    END

    INSERT INTO @Integers ( [IntValue] )
    SELECT (Thousands.Digit * 1000) + (Hundreds.Digit * 100) + 
           (Tens.Digit * 10) + Ones.Digit
    FROM @Digits Thousands, @Digits Hundreds, @Digits Tens, @Digits Ones
    WHERE (Thousands.Digit * 1000) + (Hundreds.Digit * 100) + 
          (Tens.Digit * 10) + Ones.Digit BETWEEN 1 AND @MaxValue
    ORDER BY 1

    RETURN
END
GO

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ufnMin')
DROP FUNCTION ufnMin
GO

CREATE FUNCTION ufnMin(
	@STR VARCHAR(8000),
	@separator VARCHAR(16)=',')
RETURNS INT
AS

/* Splits passed string into items based on the specified separator string
Parameters:
	@str - The string to split
	@separator - The separator string ( comma is default)

Returns minimum integer of the list of integers - no error checking

Test:

declare @min int
select @min = dbo.ufnMin('1,5,7,9,10,-32',',')
print @min

*/

BEGIN
	DECLARE @Item VARCHAR(128), @pos INT, @MinValue int, @CurValue int
	SET @MinValue = 0
	WHILE DATALENGTH(@STR) > 0
	BEGIN
		SET @pos = CHARINDEX(@separator, @STR)
		IF @pos = 0 SET @pos = DATALENGTH(@STR)+1
		SET @Item = LEFT(@STR, @pos -1 )
		SET @CurValue = CAST(@Item as Int)
		IF @CurValue <= @MinValue SET @MinValue = @CurValue
		SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)
		--INSERT INTO @TableArray (Item) VALUES(@Item)
	END
	RETURN @MinValue
END
GO

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sysobjects WHERE name = N'ufnMax')
DROP FUNCTION ufnMax
GO

CREATE FUNCTION ufnMax(
	@STR VARCHAR(8000),
	@separator VARCHAR(16)=',')
RETURNS INT
AS

/* Splits passed string into items based on the specified separator string

Parameters:
	@str - The string to split
	@separator - The separator string ( comma is default)

Returns minimum integer of the list of integers - no error checking

Test:

declare @max int
select @max = dbo.ufnMax('1,5,7,9,10,-32',',')
print @max

*/

BEGIN
	DECLARE @Item VARCHAR(128), @pos INT, @MaxValue int, @CurValue int
	SET @MaxValue = 0
	WHILE DATALENGTH(@STR) > 0
	BEGIN
		SET @pos = CHARINDEX(@separator, @STR)
		IF @pos = 0 SET @pos = DATALENGTH(@STR)+1
		SET @Item = LEFT(@STR, @pos -1 )
		SET @CurValue = CAST(@Item as Int)
		IF @CurValue > @MaxValue SET @MaxValue = @CurValue
		SET @STR = SUBSTRING(@STR, @pos + DATALENGTH(@separator), 8000)
		--INSERT INTO @TableArray (Item) VALUES(@Item)
	END
	RETURN @MaxValue
END
GO

/* 
----------------------------------------------------------------------
-- Step #1: Create Table and Populate with Values
CREATE TABLE #MissingID ( [ID] INT IDENTITY, [Name] VARCHAR(20) )

INSERT INTO #MissingID ( [Name] ) VALUES ( 'Bentley' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'BMW' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Ferrari' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Lamborghini' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Hummer' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Jaguar' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Lexus' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Mercedes Benz' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Porsche' )
INSERT INTO #MissingID ( [Name] ) VALUES ( 'Volvo' )

SELECT * FROM #MissingID

-- Step #2: Delete IDs
DELETE FROM #MissingID WHERE [ID] IN (3, 4, 9)

SELECT * FROM #MissingID

-- Step #3: Identify Missing IDENTITY Values
DECLARE @MaxID INT
SELECT @MaxID = [ID] FROM #MissingID

SELECT A.*
FROM [dbo].[ufnGenerateListIntegers](@MaxID) A 
LEFT OUTER JOIN #MissingID B 
ON A.[IntValue] = B.[ID] 
WHERE B.[ID] IS NULL
----------------------------------------------------------------------
*/


/*----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------
  -- STORED PROCEDURE --
  ----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------*/

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetAllLocalizedData]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetAllLocalizedData]
GO

CREATE PROCEDURE [dbo].[uspGetAllLocalizedData] 
AS
BEGIN
    SELECT [Identifier],[Language],[Title],[Description] FROM [dbo].[LocalizedData] ORDER BY [Identifier]
END
GO

----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetAllMenus]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetAllMenus]
GO

CREATE PROCEDURE [dbo].[uspGetAllMenus]	
AS
-- EXEC [dbo].[uspGetAllMenus]
BEGIN
	
	SELECT [Id]
	      ,[ParentId]
	      ,[Icon]
	      ,[Caption]
	      ,[Tooltip]
	      ,[Description]
	      ,[Action]
	      ,[State]
	      ,[Type]
	      ,[SortOrder]
	FROM [dbo].[Menus]

END
GO

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetCompaniesByParent]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetCompaniesByParent]
GO

CREATE PROCEDURE [dbo].[uspGetCompaniesByParent]	
    @ParentId VARCHAR(10)--Tạo bởi người dùng
AS
-- EXEC [dbo].[uspGetCompaniesByParent] ''
BEGIN    

	SELECT Id,Name,ParentId
	FROM [Companies] WHERE [ParentId] = @ParentId
	Order By [Id]

END
GO

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspAddUserToOrg]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspAddUserToOrg]
GO

CREATE PROCEDURE [dbo].[uspAddUserToOrg] (
    @userId uniqueidentifier,
    @orgId varchar(10)
)
AS
-- EXEC [dbo].[uspAddUserToOrg] '',''
BEGIN
    INSERT INTO [dbo].[UsersInRoles](UserId,OrgId) VALUES(@userId,@orgId)
END
GO

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspAddManyUsersToOrg]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspAddManyUsersToOrg]
GO

CREATE PROCEDURE [dbo].[uspAddManyUsersToOrg] (
    @orgId  varchar(10),
    @userIds VARCHAR(MAX)
)
AS
-- EXEC [dbo].[uspAddManyUsersToOrg] '', ''
BEGIN

	IF OBJECT_ID('tempdb..#UsersInOrg') IS NOT NULL
	DROP TABLE #UsersInOrg
	CREATE TABLE #UsersInOrg (OrgId VARCHAR(10), UserId uniqueidentifier) 
	DELETE FROM #UsersInOrg

	INSERT #UsersInOrg (OrgId,UserId) 
	SELECT Data, @orgId FROM [dbo].[ufnSplit](@userIds,',')
	
	INSERT INTO [dbo].[UsersInRoles](OrgId,UserId)
	SELECT OrgId,UserId FROM #UsersInOrg
END
GO

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspAddUserToManyOrgs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspAddUserToManyOrgs]
GO

CREATE PROCEDURE [dbo].[uspAddUserToManyOrgs] (
    @userId uniqueidentifier,
    @orgIds VARCHAR(MAX)
)
AS
-- EXEC [dbo].[uspAddUserToManyOrgs] '', ''
BEGIN

	IF OBJECT_ID('tempdb..#UsersInOrg') IS NOT NULL
	DROP TABLE #UsersInOrg
	CREATE TABLE #UsersInOrg (OrgId VARCHAR(10), UserId uniqueidentifier) 
	DELETE FROM #UsersInOrg

	INSERT #UsersInOrg (OrgId,UserId) 
	SELECT Data, @userId FROM [dbo].[ufnSplit](@orgIds,',')
	
	INSERT INTO [dbo].[UsersInRoles](OrgId,UserId)
	SELECT OrgId,UserId FROM #UsersInOrg
END
GO


----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspRemoveUserFromOrg]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspRemoveUserFromOrg]
GO

CREATE PROCEDURE [dbo].[uspRemoveUserFromOrg] (
    @userId uniqueidentifier,
    @orgId varchar(10)
)
AS
-- EXEC [dbo].[uspRemoveUserFromOrg] 1,1
BEGIN    
    DELETE FROM UsersInRoles WHERE UserId = @userId AND OrgId = @orgId
END
GO

----------------------------------------------------------------------------------------------------

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspRemoveUserFromManyOrgs]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspRemoveUserFromManyOrgs]
GO

CREATE PROCEDURE [dbo].[uspRemoveUserFromManyOrgs] (
    @userId uniqueidentifier,
    @orgIds VARCHAR(MAX)
)
AS
-- EXEC [dbo].[uspRemoveUserFromManyOrgs] 1, '1,2,3'
BEGIN    

	IF OBJECT_ID('tempdb..#UsersInOrg') IS NOT NULL
	DROP TABLE #UsersInOrg
	CREATE TABLE #UsersInOrg (OrgId VARCHAR(10), UserId uniqueidentifier) 
	DELETE FROM #UsersInOrg

	INSERT #UsersInOrg (OrgId,UserId) 
	SELECT Data, @userId FROM [dbo].[ufnSplit](@orgIds,',')
	
	INSERT INTO [dbo].[UsersInRoles](OrgId,UserId)
	SELECT OrgId,UserId FROM #UsersInOrg

    DELETE FROM UsersInRoles WHERE UserId = @userId 
	AND OrgId IN (SELECT OrgId FROM #UsersInOrg)
END
GO

----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetAllOrganizations]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetAllOrganizations]
GO

CREATE PROCEDURE [dbo].[uspGetAllOrganizations]	
AS
-- EXEC [dbo].[uspGetAllOrganizations]
BEGIN
	
	SELECT Id, Name
	FROM [dbo].[Organizations]

END
GO
----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspGetAllCompanies]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspGetAllCompanies]
GO

CREATE PROCEDURE [dbo].[uspGetAllCompanies]	
AS
-- EXEC [dbo].[uspGetAllCompanies]
BEGIN
	
	SELECT Id, Name
	FROM [dbo].[Companies]

END
GO

----------------------------------------------------------------------------------------------------
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetLocalizedData]') AND type in (N'P', N'PC'))
DROP PROC [dbo].[GetLocalizedData]
GO

--
-- create procedure to retrieve some localized strings
--

CREATE PROCEDURE [dbo].[GetLocalizedData](
  @identifier varchar(50),
  @fallbackLanguage char(2) = 'en',
  @lang varchar(5)
)
AS
--
--EXEC GetLocalizedData @identifier = 'String1', @lang = 'vi'
--EXEC GetLocalizedData @identifier = 'String1', @lang = 'en'
--EXEC GetLocalizedData @identifier = 'String1', @lang = 'en-us'

--EXEC GetLocalizedData @identifier = 'String2', @lang = 'en-us'
--EXEC GetLocalizedData @identifier = 'String2', @lang = 'en'
--EXEC GetLocalizedData @identifier = 'String2', @lang = 'vi'
--
  DECLARE @generalLanguage char(2)
        , @specializedLanguage char(5)
 
  IF LEN(@lang) = 5
    BEGIN
      SET @specializedLanguage = @lang
      SET @generalLanguage = LEFT(@lang, 2)
    END
  ELSE
    BEGIN
      SET @specializedLanguage = @lang + '-' + @lang
      SET @generalLanguage = @lang
    END
    SELECT COALESCE([S].[Title], [G].[Title], [F].[Title])
         , COALESCE([S].[Description], [G].[Description], [F].[Description])
      FROM [LocalizedData] [F]
 LEFT JOIN [LocalizedData] [G]
        ON [G].[Identifier] = @identifier
       AND [G].[Language] = @generalLanguage
 LEFT JOIN [LocalizedData] [S]
        ON [S].[Identifier] = @identifier
       AND [S].[Language] = @specializedLanguage
     WHERE [F].[Identifier] = @identifier
       AND [F].[Language] = @fallbackLanguage
GO

/*----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------

  -- VIEW SECURITY --

  ----------------------------------------------------------------------------------------------------------------
  ---------------------------------------------------------------------------------------------------------------*/

--select dbo.ufnGetName(UR.UserId) AS [UserName]
--, R.RoleName
--, O.Name AS [OrgName]
--from UsersInRoles UR
--join Organizations O on UR.OrgId = O.Id
--join Roles R on R.RoleId = UR.RoleId


--select dbo.ufnGetName(UR.UserId) AS [UserName]
--, R.RoleName
--, O.Name AS [OrgName]
--from UsersInRoles UR
--join Companies C on UR.OrgId = C.Id
--join Roles R on R.RoleId = UR.RoleId


/*----------------------------------------------------------------------------------------------------------------
  ----------------------------------------------------------------------------------------------------------------
  -- TRIGGER --
  ---------------------------------------------------------------------------------------------------------------- 
  ---------------------------------------------------------------------------------------------------------------*/

IF OBJECT_ID ('[dbo].[aspnet_Membership_InsertIntoUsers]', 'TR') IS NOT NULL
DROP TRIGGER [dbo].[aspnet_Membership_InsertIntoUsers];
GO

CREATE TRIGGER [dbo].[aspnet_Membership_InsertIntoUsers]
ON [dbo].[aspnet_Membership]
AFTER INSERT
AS
BEGIN
	INSERT INTO [dbo].[Users] (UserId,UserName,Email) 
	SELECT UserId,(SELECT TOP 1 AU.UserName FROM [dbo].[aspnet_Users] AU
		INNER JOIN [dbo].[aspnet_Membership] M ON AU.UserId = M.UserId
		INNER JOIN INSERTED I ON M.UserId = I.UserId),Email
	FROM INSERTED
END
GO

----------------------------------------------------------------------------------------------------

IF OBJECT_ID ('[dbo].[aspnet_Membership_DeleteFromUsers]', 'TR') IS NOT NULL
DROP TRIGGER [dbo].[aspnet_Membership_DeleteFromUsers];
GO

CREATE TRIGGER [dbo].[aspnet_Membership_DeleteFromUsers]
ON [dbo].[aspnet_Membership]
AFTER DELETE
AS
BEGIN
	DELETE FROM [dbo].[UsersInRoles] WHERE UserId in (
		SELECT U.UserId FROM [dbo].[Users] U
		INNER JOIN [aspnet_Users] AU
		ON AU.UserId = U.UserId
		INNER JOIN DELETED D
		ON U.UserId = D.UserId)
	DELETE FROM [dbo].[Users] WHERE UserId in (SELECT UserId FROM DELETED)
END
GO